package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.Customer;
import ModelLayer.Invoice;
import ModelLayer.SalesOrder;

public class DBSalesOrder implements IFDBSalesOrder {

	private Connection con;

	public DBSalesOrder() {

		con = DbConnection1.getInstance().getDBcon();
	}

	@Override
	public ArrayList<SalesOrder> getAllSalesOrders(boolean retriveAssociation) {
		return miscWhere("", retriveAssociation);
	}

	private ArrayList<SalesOrder> miscWhere(String wClause,
			boolean retriveAssociation) {
		ResultSet results;
		ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();

		String query = buildQuery(wClause);

		try { // read the sales orders from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {

				SalesOrder salesOrderObj = new SalesOrder();
				salesOrderObj = buildSalesOrder(results);

				// association is to be build

				if (retriveAssociation) {

					IFDBCustomer dbcustomer = new DBCustomer();
					Customer customer = dbcustomer.findCustomer(salesOrderObj
							.getCustomer().getCprNo(), false);
					salesOrderObj.setCustomer(customer);
					System.out.println("Customer is selected");

					IFDBInvoice dbinvoice= new DBInvoice();
					Invoice invoice = dbinvoice.findInvoice(salesOrderObj.getInvoice().getInvNo(), retriveAssociation);
					salesOrderObj.setInvoice(invoice);
					System.out.println("Invoice is selected");

				}

				list.add(salesOrderObj);

				stmt.close();
			}
		}

		catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public SalesOrder findSalesOrder(int saleID, boolean retriveAssociation) {
		String wClause = " Sales_ID = '" + saleID + "'";
		return singleWhere(wClause, retriveAssociation);
	}

	private SalesOrder singleWhere(String wClause, boolean retriveAssociation) {
		ResultSet results;
		SalesOrder salesOrderObj = new SalesOrder();

		String query = buildQuery(wClause);
		System.out.println(query);
		try { // read the SalesOrder from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				salesOrderObj = buildSalesOrder(results);

				stmt.close();
				if (retriveAssociation) { // The zipCode and Club are to be
											// build as well

					IFDBCustomer dbcustomer = new DBCustomer();
					Customer customer = dbcustomer.findCustomer(salesOrderObj
							.getCustomer().getCprNo(), false);
					salesOrderObj.setCustomer(customer);
					System.out.println("Customer is selected");

					IFDBInvoice dbinvoice= new DBInvoice();
					Invoice invoice = dbinvoice.findInvoice(salesOrderObj.getInvoice().getInvNo(), retriveAssociation);
					salesOrderObj.setInvoice(invoice);
					System.out.println("Invoice is selected");

				}
			} else { // no customer was found
				salesOrderObj = null;
			}
		}// end try
		catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return salesOrderObj;
	}

	private SalesOrder buildSalesOrder(ResultSet results) {
		SalesOrder salesOrderObj = new SalesOrder();
		try {

			salesOrderObj.setSaleID(results.getInt(1));
			salesOrderObj.setDate(results.getString(2));
			salesOrderObj.settAmount(results.getDouble(3));
			salesOrderObj.setStatus(results.getString(4));
			salesOrderObj.setDeliveryDate(results.getString(5));
			salesOrderObj.setCustomer(new Customer(results.getString(6)));
			salesOrderObj.setInvoice(new Invoice(results.getInt(7)));
		} catch (Exception e) {
			System.out.println("error in building the sales order object");
		}
		return salesOrderObj;
	}

	// method to build a query
	private String buildQuery(String wClause) {
		String query = "SELECT Sale_ID, Date, T_Amount, Delivery_Status, Delivery_date, CPR,Inv_NO FROM SalesOrder";

		if (wClause.length() > 0)
			query = query + " WHERE " + wClause;

		return query;
	}

	@Override
	public int insertSalesOrder(SalesOrder order) throws Exception {
		int rc = -1;
		
		String deliveryDate=order.getDeliveryDate();
		if(order.getDeliveryDate()!=null)
		{
			
			deliveryDate="'"+deliveryDate+"'";
		}
		

		String query = "INSERT INTO SalesOrder (Sale_ID, Date, T_Amount, Delivery_Status, Delivery_date, CPR,Inv_NO) VALUES('"
				+ order.getSaleID()
				+ "', '"
				+ order.getDate()
				+ "', '"
				+ order.gettAmount()
				+ "', '"
				+ order.getStatus()
				+ "', "
				+ deliveryDate
				+ ", '"
				+ order.getCustomer().getCprNo()
				+ "', '"
				+ order.getInvoice().getInvNo()
				+ "')";

		System.out.println("insert : " + query);

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException ex) {
			System.out.println("problem while inserting sales order");
			throw new Exception("insertion was not correct");
		}

		return rc;
	}

	@Override
	public int updateSalesOrder(SalesOrder order) {
		int rc = -1;

		String query = "UPDATE SalesOrder SET " + "Date ='" + order.getDate()
				+ "'" + "T_Amount ='" + order.gettAmount() + "'"
				+ "Delivery_Status ='" + order.getStatus() + "'"
				+ "Delivery_date ='" + order.getDeliveryDate() + "'" + "CPR ='"
				+ order.getCustomer().getCprNo() + "'" + "Inv_NO ="
				+ order.getInvoice().getInvNo() +

				" WHERE Sale_ID = '" + order.getSaleID() + "'";
		System.out.println("Update query:" + query);
		try { // update SalesOrder
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}// end try
		catch (Exception ex) {
			System.out.println("Update exception in sales order db: " + ex);
		}
		return (rc);
	}

	@Override
	public int delete(SalesOrder salesOrder) {
		int rc = -1;

		String query = "DELETE FROM SalesOrder WHERE Sale_ID= '" + salesOrder.getSaleID()
				+ "'";
		System.out.println(query);
		try { // delete from OrderLine
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (Exception ex) {
			System.out.println("Delete exception in sales order db: " + ex);
		}
		return (rc);
	}
}
